import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by dbquincy on 2018/1/2.
 */

public class DBPool {
    private static DBPool dbPool;
    private ComboPooledDataSource dataSource;

    static {
        dbPool = new DBPool();
    }

    public DBPool() {
        try {
            dataSource = new ComboPooledDataSource("DD");
            //dataSource.setUser("SYSDBA");
            //dataSource.setPassword("SYSDBA");
            //dataSource.setJdbcUrl("jdbc:dm://DM110");
            //dataSource.setDriverClass("");
            //// 设置初始连接池的大小！
            //dataSource.setInitialPoolSize(50);
            //// 设置连接池的最小值！
            //dataSource.setMinPoolSize(50);
            //// 设置连接池的最大值！
            //dataSource.setMaxPoolSize(100);
            //// 设置连接池中的最大Statements数量！
            //dataSource.setMaxStatements(50);
            //// 设置连接池的最大空闲时间！
            //dataSource.setMaxIdleTime(60);
            dataSource.setAutoCommitOnClose(false);
            //dataSource.setPreferredTestQuery("select 1 from dual;");
            //dataSource.setTestConnectionOnCheckin(true);
        } catch (Exception e){//( PropertyVetoException e) {
            throw new RuntimeException(e);
        }
    }

    public final static DBPool getInstance() {
        return dbPool;
    }

    public final Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException("无法从数据源获取连接 ", e);
        }
    }

    public static void main(String[] args) throws SQLException {
        Connection con = null;
        try {
            int i = 50;
            //con = DBPool.getInstance().getConnection();
            while (i > 25) {
                i = i - 1;
                try {
                    con = DBPool.getInstance().getConnection();
                    DatabaseMetaData metaData = con.getMetaData();
                    //System.out.println(" → "+metaData.getCatalogs());
                    Thread.sleep(3000);
                    ResultSet rs = con.createStatement().executeQuery("SELECT instance_name,sysdate,'" + i + "' FROM v$instance;");
                    while (rs.next()) {
                        System.out.println(rs.getObject(1) + " → " + rs.getObject(3) + " → " + rs.getObject(2));
                    }
                    boolean rs1 = con.createStatement().execute("insert into test1 values('" + i + "');");
                    Thread.sleep(3000);
                    boolean rs2 = con.createStatement().execute("update test1 set v1 =" + i+1000 + " where v1 = "+i);
                    Thread.sleep(3000);
                    boolean rs3 = con.createStatement().execute("update test1 set v1 =" + i+2000 + " where v1 = "+i);
                    if (i%2==0){
                        con.rollback();
                    }
                    con.close();
                } catch (Exception e) {
                    System.out.println(e.getMessage());// getMessage String 类型的
                }
            }
        } catch (Exception e) {
            System.out.println("执行失败 => 失败捕获");
        } finally {
            if (con != null)
                con.close();
        }
    }
}
